#!/usr/bin/env python
# -*- coding: utf-8 -*-
import time
from tkinter import *
import sys
import datetime
import pymysql
from pymysqlreplication import BinLogStreamReader
from pymysqlreplication.event import QueryEvent, RotateEvent, FormatDescriptionEvent
from binlog2sql_util import command_line_args, concat_sql_from_binlog_event, create_unique_file, temp_open, \
    reversed_lines, is_dml_event, event_type


class Binlog2sql(object):

    def __init__(self, connection_settings, start_file=None, start_pos=None, end_file=None, end_pos=None,
                 start_time=None, stop_time=None, only_schemas=None, only_tables=None, no_pk=False,
                 flashback=False, stop_never=False, back_interval=1.0, only_dml=True, sql_type=None):
        """
        conn_setting: {'host': 127.0.0.1, 'port': 3306, 'user': user, 'passwd': passwd, 'charset': 'utf8'}
        """

        if not start_file:
            raise ValueError('Lack of parameter: start_file')

        self.conn_setting = connection_settings
        self.start_file = start_file
        self.start_pos = start_pos if start_pos else 4    # use binlog v4
        self.end_file = end_file if end_file else start_file
        self.end_pos = end_pos
        if start_time:
            self.start_time = datetime.datetime.strptime(start_time, "%Y-%m-%d %H:%M:%S")
        else:
            self.start_time = datetime.datetime.strptime('1980-01-01 00:00:00', "%Y-%m-%d %H:%M:%S")
        if stop_time:
            self.stop_time = datetime.datetime.strptime(stop_time, "%Y-%m-%d %H:%M:%S")
        else:
            self.stop_time = datetime.datetime.strptime('2999-12-31 00:00:00', "%Y-%m-%d %H:%M:%S")

        self.only_schemas = only_schemas if only_schemas else None
        self.only_tables = only_tables if only_tables else None
        self.no_pk, self.flashback, self.stop_never, self.back_interval = (no_pk, flashback, stop_never, back_interval)
        self.only_dml = only_dml
        self.sql_type = [t.upper() for t in sql_type] if sql_type else []

        self.binlogList = []
        self.connection = pymysql.connect(**self.conn_setting)
        with self.connection as cursor:
            cursor.execute("SHOW MASTER STATUS")
            self.eof_file, self.eof_pos = cursor.fetchone()[:2]
            cursor.execute("SHOW MASTER LOGS")
            bin_index = [row[0] for row in cursor.fetchall()]
            if self.start_file not in bin_index:
                raise ValueError('parameter error: start_file %s not in mysql server' % self.start_file)
            binlog2i = lambda x: x.split('.')[1]
            for binary in bin_index:
                if binlog2i(self.start_file) <= binlog2i(binary) <= binlog2i(self.end_file):
                    self.binlogList.append(binary)

            cursor.execute("SELECT @@server_id")
            self.server_id = cursor.fetchone()[0]
            if not self.server_id:
                raise ValueError('missing server_id in %s:%s' % (self.conn_setting['host'], self.conn_setting['port']))

    def process_binlog(self):
        stream = BinLogStreamReader(connection_settings=self.conn_setting, server_id=self.server_id,
                                    log_file=self.start_file, log_pos=self.start_pos, only_schemas=self.only_schemas,
                                    only_tables=self.only_tables, resume_stream=True, blocking=True)
        print('stream=','connection_settings=',self.conn_setting, 'server_id=',self.server_id,
                                    'log_file=',self.start_file, 'log_pos=',self.start_pos, 'only_schemas=',self.only_schemas,
                                    'only_tables=',self.only_tables, 'resume_stream=',True, 'blocking=',True)
        flag_last_event = False
        e_start_pos, last_pos = stream.log_pos, stream.log_pos
        # to simplify code, we do not use flock for tmp_file.
        #tmp_file = create_unique_file('%s.%s' % (self.conn_setting['host'], self.conn_setting['port']))
        tmp_file = create_unique_file('%s.%s' % (self.conn_setting['host'], self.conn_setting['port']))
        print(tmp_file)
        rollbacksql = "管理员翻译日志的时间:{}".format(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())) + '\n'
        with temp_open(tmp_file, "w") as f_tmp, self.connection as cursor:
            for binlog_event in stream:
                if not self.stop_never:
                    try:
                        event_time = datetime.datetime.fromtimestamp(binlog_event.timestamp)
                    except OSError:
                        event_time = datetime.datetime(1980, 1, 1, 0, 0)
                    if (stream.log_file == self.end_file and stream.log_pos == self.end_pos) or \
                            (stream.log_file == self.eof_file and stream.log_pos == self.eof_pos):
                        flag_last_event = True
                    elif event_time < self.start_time:
                        if not (isinstance(binlog_event, RotateEvent)
                                or isinstance(binlog_event, FormatDescriptionEvent)):
                            last_pos = binlog_event.packet.log_pos
                        continue
                    elif (stream.log_file not in self.binlogList) or \
                            (self.end_pos and stream.log_file == self.end_file and stream.log_pos > self.end_pos) or \
                            (stream.log_file == self.eof_file and stream.log_pos > self.eof_pos) or \
                            (event_time >= self.stop_time):
                        break
                    # else:
                    #     raise ValueError('unknown binlog file or position')

                if isinstance(binlog_event, QueryEvent) and binlog_event.query == 'BEGIN':
                    e_start_pos = last_pos


                if isinstance(binlog_event, QueryEvent) and not self.only_dml:
                    sql = concat_sql_from_binlog_event(cursor=cursor, binlog_event=binlog_event,
                                                       flashback=self.flashback, no_pk=self.no_pk)
                    if sql:
                        #print(sql)
                        rollbacksql += sql + '\n'
                elif is_dml_event(binlog_event) and event_type(binlog_event) in self.sql_type:
                    for row in binlog_event.rows:
                        sql = concat_sql_from_binlog_event(cursor=cursor, binlog_event=binlog_event, no_pk=self.no_pk,
                                                           row=row, flashback=self.flashback, e_start_pos=e_start_pos)
                        if self.flashback:
                            f_tmp.write(sql + '\n')
                        else:
                            #print(sql)
                            rollbacksql+=sql + '\n'
                #print(rollbacksql)
                self.writeSQL("option: \n"+rollbacksql)
                if not (isinstance(binlog_event, RotateEvent) or isinstance(binlog_event, FormatDescriptionEvent)):
                    last_pos = binlog_event.packet.log_pos
                if flag_last_event:
                    break

            stream.close()
            f_tmp.close()
            if self.flashback:
                self.print_rollback_sql(filename=tmp_file)
        return True

    def print_rollback_sql(self, filename):
        """print rollback sql from tmp_file"""
        rollbacksql = "管理员翻译日志的时间:{}".format(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())) + '\n'
        with open(filename, "rb") as f_tmp:
            batch_size = 1000
            i = 0
            for line in reversed_lines(f_tmp):
                rollbacksql +=line.rstrip()+'\n'
                if i >= batch_size:
                    i = 0
                    if self.back_interval:
                        print('SELECT SLEEP(%s);' % self.back_interval)
                else:
                    i += 1
        #print(rollbacksql)
        self.writeSQL("rollback: \n"+rollbacksql)
    def __del__(self):
        pass

    def writeSQL(self,sql):
        with open('D:'+self.start_file+'.sql', 'w', encoding='utf-8') as f:
            f.write(sql)

def translationMySqlLog():
    start_file = entry1.get().strip()
    # start_pos = entry2.get().strip()
    # end_file = entry3.get().strip()
    # end_pos = entry4.get().strip()
    start_time = entry5.get().strip()
    # stop_time = entry6.get().strip()
    databases = entry7.get().strip()
    tables = entry8.get().strip()
    # no_pk = entry9.get().strip()
    # flashback = entry10.get().strip()
    # stop_never = entry11.get().strip()
    # back_interval = entry12.get().strip()
    # only_dml = entry13.get().strip()
    # sql_type = entry14.get().strip()
    host = entry15.get().strip()
    port = entry16.get().strip()
    user = entry17.get().strip()
    password = entry18.get().strip()

    abc = ['-h'+host, '-P'+port, '-u'+user, '-p'+password, '-d'+databases, '-t'+tables,
           '--start-file='+start_file , '--start-datetime='+start_time]
    args = command_line_args(abc)
    conn_setting = {'host': args.host, 'port': args.port, 'user': args.user, 'passwd': args.password,
                    'charset': 'utf8mb4'}
    binlog2sql = Binlog2sql(connection_settings=conn_setting, start_file=args.start_file, start_pos=args.start_pos,
                            end_file=args.end_file, end_pos=args.end_pos, start_time=args.start_time,
                            stop_time=args.stop_time, only_schemas=args.databases, only_tables=args.tables,
                            no_pk=args.no_pk, flashback=args.flashback, stop_never=args.stop_never,
                            back_interval=args.back_interval, only_dml=args.only_dml, sql_type=args.sql_type)
    binlog2sql.process_binlog()

if __name__ == '__main__':
    #方式一，在terminal中执行yanghua.md文件中的语句
    # print('sys.argv[1:] = ',sys.argv[1:])
    # abc =['-h127.0.0.1', '-P3306', '-uroot', '-proot', '-dyanghua0', '-tsys_dictionary', '--start-file=mysql-bin.000027']
    # #args = command_line_args(sys.argv[1:])
    # args = command_line_args(abc)
    # # print("无效入参", 'start_pos =',args.start_pos,'end_file =',args.end_file,'end_pos =',args.end_pos,'stop_time =',args.stop_time,'no_pk =',args.no_pk,'stop_never =',args.stop_never,'back_interval =',args.back_interval,'only_dml =',args.only_dml,'sql_type =',args.sql_type)
    # # print("有效入参", 'start_file =',args.start_file,'start_time =',args.start_time,'flashback =',args.flashback,'host =',args.host,'port =', args.port, 'user =',args.user, 'password =', args.password,'databases =',args.databases,'tables =',args.tables,'start_file =',args.start_file)
    # # print("有效入参", 'conn_setting =',conn_setting)
    # conn_setting = {'host': args.host, 'port': args.port, 'user': args.user, 'passwd': args.password, 'charset': 'utf8mb4'}
    # binlog2sql = Binlog2sql(connection_settings=conn_setting, start_file=args.start_file, start_pos=args.start_pos,
    #                         end_file=args.end_file, end_pos=args.end_pos, start_time=args.start_time,
    #                         stop_time=args.stop_time, only_schemas=args.databases, only_tables=args.tables,
    #                         no_pk=args.no_pk, flashback=args.flashback, stop_never=args.stop_never,
    #                         back_interval=args.back_interval, only_dml=args.only_dml, sql_type=args.sql_type)
    # binlog2sql.process_binlog()

    #方式二，这就是为了打包成exe而准备的
    # Label(父对象, text=“标签内容”, width =“宽度”, height =“高度”, anchor =“对齐方式”)
    # width ：标签宽度，单位（字符）
    # height：标签的高度
    # 单位（行）
    # anchor：对齐方式
    # 可选内容包括
    # n: 靠上
    # w靠左
    # s靠下
    # e靠右
    # 或者组合使用，，如nw
    # 左上方对齐
    root = Tk()
    root.title('MySQL日志翻译,作者:杨二狗,输出文件在D盘')
    root.geometry('400x300+500+200')
    root.resizable(False, False)

    start_file = Label(root, text='MySQL日志文件名', font=('黑体', 18), fg='blue' )
    start_file.grid()
    entry1 = Entry(root)
    entry1.grid(row=0, column=1)
    entry1.insert(0, "mysql-bin.000027")

    # start_pos = Label(root, text='start_pos', font=('黑体', 18), fg='blue')
    # start_pos.grid(row=1, column=0)
    # entry2 = Entry(root)
    # entry2.grid(row=1, column=1)
    # entry2.insert(0, 4)

    # end_file = Label(root, text='end_file', font=('黑体', 18), fg='blue')
    # end_file.grid(row=2, column=0)
    # entry3 = Entry(root)
    # entry3.grid(row=2, column=1)
    # entry3.insert(0, "")
    #
    # end_pos = Label(root, text='end_pos', font=('黑体', 18), fg='blue')
    # end_pos.grid(row=3, column=0)
    # entry4 = Entry(root)
    # entry4.grid(row=3, column=1)
    # entry4.insert(0, "0")
    #
    start_time = Label(root, text='误操作的时间点', font=('黑体', 18), fg='blue')
    start_time.grid(row=4, column=0)
    entry5 = Entry(root)
    entry5.grid(row=4, column=1)
    entry5.insert(0, '2021-11-09 14:30:00')

    # stop_time = Label(root, text='stop_time', font=('黑体', 18), fg='blue')
    # stop_time.grid(row=5, column=0)
    # entry6 = Entry(root)
    # entry6.grid(row=5, column=1)
    # entry6.insert(0, "")

    databases = Label(root, text='数据库名', font=('黑体', 18), fg='blue')
    databases.grid(row=6, column=0)
    entry7 = Entry(root)
    entry7.grid(row=6, column=1)
    entry7.insert(0, "yanghua0")

    tables = Label(root, text='表名', font=('黑体', 18), fg='blue')
    tables.grid(row=7, column=0)
    entry8 = Entry(root)
    entry8.grid(row=7, column=1)
    entry8.insert(0, "sys_dictionary")

    # no_pk = Label(root, text='no_pk', font=('黑体', 18), fg='blue')
    # no_pk.grid(row=8, column=0)
    # entry9 = Entry(root)
    # entry9.grid(row=8, column=1)
    # entry9.insert(0, False)

    # flashback = Label(root, text='flashback默认False', font=('黑体', 18), fg='blue')
    # flashback.grid(row=9, column=0)
    # entry10 = Entry(root)
    # entry10.grid(row=9, column=1)
    # entry10.insert(0, False)

    # stop_never = Label(root, text='stop_never默认', font=('黑体', 18), fg='blue')
    # stop_never.grid(row=10, column=0)
    # entry11 = Entry(root)
    # entry11.grid(row=10, column=1)
    # entry11.insert(0, False)
    #
    # back_interval = Label(root, text='back_interval默认', font=('黑体', 18), fg='blue')
    # back_interval.grid(row=11, column=0)
    # entry12 = Entry(root)
    # entry12.grid(row=11, column=1)
    # entry12.insert(0, 1.0)

    # only_dml = Label(root, text='only_dml默认', font=('黑体', 18), fg='blue')
    # only_dml.grid(row=12, column=0)
    # entry13 = Entry(root)
    # entry13.grid(row=12, column=1)
    # entry13.insert(0, False)
    #
    # sql_type = Label(root, text='sql_type默认INSERT, UPDATE, DELETE', font=('黑体', 18), fg='blue')
    # sql_type.grid(row=13, column=0)
    # entry14 = Entry(root)
    # entry14.grid(row=13, column=1)
    # entry14.insert(0, 'DELETE')

    host = Label(root, text='数据库的ip', font=('黑体', 18), fg='blue')
    host.grid(row=14, column=0)
    entry15 = Entry(root)
    entry15.grid(row=14, column=1)
    entry15.insert(0, "localhost")

    port = Label(root, text='数据库端口号', font=('黑体', 18), fg='blue')
    port.grid(row=15, column=0)
    entry16 = Entry(root)
    entry16.grid(row=15, column=1)
    entry16.insert(0, 3306)

    user = Label(root, text='用户名', font=('黑体', 18), fg='blue')
    user.grid(row=16, column=0)
    entry17 = Entry(root)
    entry17.grid(row=16, column=1)
    entry17.insert(0, "root")

    password = Label(root, text='密码', font=('黑体', 18), fg='blue')
    password.grid(row=17, column=0)
    entry18 = Entry(root)
    entry18.grid(row=17, column=1)
    entry18.insert(0, "root")

    button = Button(root, text='翻译', width=10, command=translationMySqlLog).grid(row=18, column=0)
    button2 = Button(root, text='退出', width=10, command=root.quit).grid(row=18, column=1)

    root.mainloop()
